package model

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/tal-tech/go-zero/core/stores/cache"
	"github.com/tal-tech/go-zero/core/stores/sqlc"
	"github.com/tal-tech/go-zero/core/stores/sqlx"
	"github.com/tal-tech/go-zero/core/stringx"
	"github.com/tal-tech/go-zero/tools/goctl/model/sql/builderx"
)

var (
	tbStatusFieldNames          = builderx.RawFieldNames(&TbStatus{})
	tbStatusRows                = strings.Join(tbStatusFieldNames, ",")
	tbStatusRowsExpectAutoSet   = strings.Join(stringx.Remove(tbStatusFieldNames, "`id`", "`create_time`", "`update_time`"), ",")
	tbStatusRowsWithPlaceHolder = strings.Join(stringx.Remove(tbStatusFieldNames, "`id`", "`create_time`", "`update_time`"), "=?,") + "=?"

	cacheTbStatusIdPrefix = "cache::tbStatus:id:"
)

type (
	TbStatusModel interface {
		Insert(data TbStatus) (sql.Result, error)
		FindOne(id int64) (*TbStatus, error)
		Update(data TbStatus) error
		Delete(id int64) error
		FindAllStatus(page, limit int, city string) (int64, []*TbStatus, error)
		FindAllOffline(page, limit int, city string) (int64, []*TbStatus, error)
		FindStatistics(city string) (Statistics, error)
	}

	defaultTbStatusModel struct {
		sqlc.CachedConn
		table string
	}

	TbStatus struct {
		Id     int64  `db:"id"`
		Sn     string `db:"sn"`     // 设备唯一号
		Posno  string `db:"posno"`  // 终端编号
		City   string `db:"city"`   // 城市代码
		Tyid   string `db:"tyid"`   // 设备类型
		Sver   string `db:"sver"`   // 软件版本号
		Carno  string `db:"carno"`  // 车辆编号
		Unum1  int64  `db:"unum1"`  // 未传记录数--公交
		Unum2  int64  `db:"unum2"`  // 未传记录数--三方
		Ndate  string `db:"ndate"`  // 当前日期
		Ntime  string `db:"ntime"`  // 当前时间
		Amount int64  `db:"amount"` // 当班总额
		Count  int64  `db:"count"`  // 当班人数
		Line   int64  `db:"line"`   // 线路编号
		Stime  string `db:"stime"`  // 开机时间
		Ctime  string `db:"ctime"`  // 关机时间
		Pver   int64  `db:"pver"`   // 票价版本
		Bver   int64  `db:"bver"`   // 黑名单版本
		Empid  string `db:"empid"`  // 司机编号
		Jd     string `db:"jd"`     // 经度
		Wd     string `db:"wd"`     // 维度
		Rvs1   string `db:"rvs1"`   // 保留
		Rvs2   string `db:"rvs2"`   // 保留备用
		Tenant int64  `db:"tenant"` // 租户号
		Alarm  int64  `db:"alarm"`  // 设备报警码
		Cmd    int64  `db:"cmd"`    // 控制命令
	}

	Statistics struct {
		Count1 int64 //设备总数
		Count2 int64 //当天运行数量
		Count3 int64 //在线数量 统计1小时内在线的

		Unum1 int64 //未传记录数--公交
		Unum2 int64 //未传记录数
	}
)

func NewTbStatusModel(conn sqlx.SqlConn, c cache.CacheConf) TbStatusModel {
	return &defaultTbStatusModel{
		CachedConn: sqlc.NewConn(conn, c),
		table:      "`tb_status`",
	}
}

func (m *defaultTbStatusModel) Insert(data TbStatus) (sql.Result, error) {
	query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", m.table, tbStatusRowsExpectAutoSet)
	ret, err := m.ExecNoCache(query, data.Sn, data.Posno, data.City, data.Tyid, data.Sver, data.Carno, data.Unum1, data.Unum2, data.Ndate, data.Ntime, data.Amount, data.Count, data.Line, data.Stime, data.Ctime, data.Pver, data.Bver, data.Empid, data.Jd, data.Wd, data.Rvs1, data.Rvs2, data.Tenant, data.Alarm, data.Cmd)

	return ret, err
}

func (m *defaultTbStatusModel) FindOne(id int64) (*TbStatus, error) {
	tbStatusIdKey := fmt.Sprintf("%s%v", cacheTbStatusIdPrefix, id)
	var resp TbStatus
	err := m.QueryRow(&resp, tbStatusIdKey, func(conn sqlx.SqlConn, v interface{}) error {
		query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", tbStatusRows, m.table)
		return conn.QueryRow(v, query, id)
	})
	switch err {
	case nil:
		return &resp, nil
	case sqlc.ErrNotFound:
		return nil, ErrNotFound
	default:
		return nil, err
	}
}

func (m *defaultTbStatusModel) Update(data TbStatus) error {
	tbStatusIdKey := fmt.Sprintf("%s%v", cacheTbStatusIdPrefix, data.Id)
	_, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
		query := fmt.Sprintf("update %s set %s where `id` = ?", m.table, tbStatusRowsWithPlaceHolder)
		return conn.Exec(query, data.Sn, data.Posno, data.City, data.Tyid, data.Sver, data.Carno, data.Unum1, data.Unum2, data.Ndate, data.Ntime, data.Amount, data.Count, data.Line, data.Stime, data.Ctime, data.Pver, data.Bver, data.Empid, data.Jd, data.Wd, data.Rvs1, data.Rvs2, data.Tenant, data.Alarm, data.Cmd, data.Id)
	}, tbStatusIdKey)
	return err
}

func (m *defaultTbStatusModel) Delete(id int64) error {

	tbStatusIdKey := fmt.Sprintf("%s%v", cacheTbStatusIdPrefix, id)
	_, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
		query := fmt.Sprintf("delete from %s where `id` = ?", m.table)
		return conn.Exec(query, id)
	}, tbStatusIdKey)
	return err
}

func (m *defaultTbStatusModel) formatPrimary(primary interface{}) string {
	return fmt.Sprintf("%s%v", cacheTbStatusIdPrefix, primary)
}

func (m *defaultTbStatusModel) queryPrimary(conn sqlx.SqlConn, v, primary interface{}) error {
	query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", tbStatusRows, m.table)
	return conn.QueryRow(v, query, primary)
}

//查找所有终端的最新状态
func (m *defaultTbStatusModel) FindAllStatus(page, limit int, city string) (int64, []*TbStatus, error) {
	var resp []*TbStatus
	var count int64

	queryCount := fmt.Sprintf("select count(1) as count FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ?", m.table, m.table)
	err := m.CachedConn.QueryRowNoCache(&count, queryCount, city)
	if err != nil {
		return 0, nil, err
	}
	query := fmt.Sprintf("select * FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ? ORDER BY id desc limit ?,?", m.table, m.table)
	err = m.CachedConn.QueryRowsNoCache(&resp, query, city, (page-1)*limit, limit)
	switch err {
	case nil:
		return count, resp, nil
	case sqlc.ErrNotFound:
		return 0, nil, ErrNotFound
	default:
		return 0, nil, err
	}
}

//查找所有离线的终端
func (m *defaultTbStatusModel) FindAllOffline(page, limit int, city string) (int64, []*TbStatus, error) {
	var resp []*TbStatus
	var count int64

	t := time.Now().Format("2006-01-02")

	queryCount := fmt.Sprintf("select count(1) as count FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ? AND ndate < ?", m.table, m.table)
	err := m.CachedConn.QueryRowNoCache(&count, queryCount, city, t)
	if err != nil {
		return 0, nil, err
	}
	query := fmt.Sprintf("select * FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ? AND ndate < ? ORDER BY id desc limit ?,?", m.table, m.table)
	err = m.CachedConn.QueryRowsNoCache(&resp, query, city, t, (page-1)*limit, limit)
	switch err {
	case nil:
		return count, resp, nil
	case sqlc.ErrNotFound:
		return 0, nil, ErrNotFound
	default:
		return 0, nil, err
	}
}

//终端统计
func (m *defaultTbStatusModel) FindStatistics(city string) (Statistics, error) {
	stat := Statistics{}
	t := time.Now().Format("2006-01-02")
	stamp := time.Now().Unix() - 60*60 //1小时前
	t1 := time.Unix(int64(stamp), 0).Format("15:04:05")

	//设备总数
	queryCount := fmt.Sprintf("select count( DISTINCT sn) as count FROM %s WHERE city = ?", m.table)
	err := m.CachedConn.QueryRowNoCache(&stat.Count1, queryCount, city)
	if err != nil {
		return stat, err
	}
	//当天运行数
	queryCount = fmt.Sprintf("select count( DISTINCT sn) as count FROM %s WHERE city = ? AND ndate = ?", m.table)
	err = m.CachedConn.QueryRowNoCache(&stat.Count2, queryCount, city, t)
	if err != nil {
		return stat, err
	}
	//统计1小时内在线数
	queryCount = fmt.Sprintf("select count( DISTINCT sn) as count FROM %s WHERE city = ? AND ndate = ? AND ntime >= ?", m.table)
	err = m.CachedConn.QueryRowNoCache(&stat.Count3, queryCount, city, t, t1)
	if err != nil {
		return stat, err
	}
	//未传记录数
	queryCount = fmt.Sprintf("select count(unum1) as count FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ?", m.table, m.table)
	err = m.CachedConn.QueryRowNoCache(&stat.Unum1, queryCount, city)
	if err != nil {
		return stat, err
	}
	queryCount = fmt.Sprintf("select count(unum2) as count FROM %s WHERE id IN (SELECT MAX(id) FROM %s GROUP BY sn) AND city = ?", m.table, m.table)
	err = m.CachedConn.QueryRowNoCache(&stat.Unum2, queryCount, city)
	if err != nil {
		return stat, err
	}

	return stat, nil
}
